﻿using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Buecherverleih;
using MySql.Data.MySqlClient;

namespace Buecherverleih
{
   
    //Kommentieren bitte
    public partial class Form1 : Form
    {
        //Click Event der dynamisch generierten Buttons aller Bücher
        public void show_books_Click(object sender, System.EventArgs e)
        {
            int bookswitch = Convert.ToInt32(((Button)sender).Name.ToString());

            if (bookswitch == 1)
            {
                alleBuecherAnzeigen();
            }
            else
            {
                alleBuecherAnzeigen((bookswitch - 50));
            }
        }

        public void erstelleBuecherMenue()
        {
            int allBooksCounted = countAllBooks();
            int bookscounter = 1;

            pnl_Search_all_Buttonlist.Controls.Clear();
            Button[] Buttons = new Button[(allBooksCounted)];

            Buttons[0] = new Button();
            Buttons[0].Left = 6;
            Buttons[0].Top = 3;
            Buttons[0].Width = 80;
            Buttons[0].Height = 38;
            Buttons[0].Font = new Font(Buttons[0].Font,
                FontStyle.Bold);
            Buttons[0].Name = "1";
            Buttons[0].Text = "Alle anzeigen";
            Buttons[0].Click += new EventHandler(show_books_Click);

            pnl_Search_all_Buttonlist.Controls.Add(Buttons[0]);

            while ((bookscounter * 50) < (allBooksCounted + 50))
            {
                Buttons[(bookscounter + 1)] = new Button();
                Buttons[(bookscounter + 1)].Left = 31 + (61 * bookscounter);
                Buttons[(bookscounter + 1)].Top = 5;
                Buttons[(bookscounter + 1)].Width = 54;
                Buttons[(bookscounter + 1)].Height = 35;
                Buttons[(bookscounter + 1)].Font = new Font(Buttons[(bookscounter + 1)].Font, 
                    FontStyle.Bold);
                Buttons[(bookscounter + 1)].Name = (bookscounter * 50).ToString();
                Buttons[(bookscounter + 1)].Text = bookscounter.ToString();
                Buttons[(bookscounter + 1)].Click += new EventHandler(show_books_Click);


                pnl_Search_all_Buttonlist.Controls.Add(Buttons[(bookscounter + 1)]);

                bookscounter++;
            }

        }

        public void alleBuecherAnzeigen()
        {
            int allBooksCounted = countAllBooks();

            pnl_Search_All_Rows.Controls.Clear();
            progressBar_allBooks.Visible = true;
            progressBar_allBooks.Refresh();
            progressBar_allBooks.Value = 0;
            progressBar_allBooks.Maximum = allBooksCounted;
            progressBar_allBooks.Step = 1;
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_books";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            int counter = 0;
            int isborrowed = 2;
            TextBox[] Textboxes = new TextBox[(4 * allBooksCounted)];
            Label[] Labels = new Label[allBooksCounted];
            Button[] Buttons = new Button[(3 * allBooksCounted)];

            while (Reader.Read())
            {
                Textboxes[(counter * 4)] = new TextBox();
                Textboxes[(counter * 4)].Text = Reader.GetValue(0).ToString();
                Textboxes[(counter * 4)].Left = 30;
                Textboxes[(counter * 4)].Top = 5 + (26 * counter);
                Textboxes[(counter * 4)].Width = 25;
                Textboxes[(counter * 4)].Height = 20;
                Textboxes[(counter * 4)].ReadOnly = true;

                Textboxes[(1 + (counter * 4))] = new TextBox();
                Textboxes[(1 + (counter * 4))].Text = Reader.GetValue(1).ToString();
                Textboxes[(1 + (counter * 4))].Left = 71;
                Textboxes[(1 + (counter * 4))].Top = 5 + (26 * counter);
                Textboxes[(1 + (counter * 4))].Width = 67;
                Textboxes[(1 + (counter * 4))].Height = 20;
                Textboxes[(1 + (counter * 4))].ReadOnly = true;

                Textboxes[(2 + (counter * 4))] = new TextBox();
                Textboxes[(2 + (counter * 4))].Text = Reader.GetValue(2).ToString();
                Textboxes[(2 + (counter * 4))].Left = 165;
                Textboxes[(2 + (counter * 4))].Top = 5 + (26 * counter);
                Textboxes[(2 + (counter * 4))].Width = 86;
                Textboxes[(2 + (counter * 4))].Height = 20;
                Textboxes[(2 + (counter * 4))].ReadOnly = true;

                Textboxes[(3 + (counter * 4))] = new TextBox();
                Textboxes[(3 + (counter * 4))].Text = Reader.GetValue(3).ToString();
                Textboxes[(3 + (counter * 4))].Left = 264;
                Textboxes[(3 + (counter * 4))].Top = 5 + (26 * counter);
                Textboxes[(3 + (counter * 4))].Width = 183;
                Textboxes[(3 + (counter * 4))].Height = 20;
                Textboxes[(3 + (counter * 4))].ReadOnly = true;

                Labels[counter] = new Label();
                Labels[counter].Left = 470;
                Labels[counter].Top = 5 + (26 * counter);
                Labels[counter].Width = 70;
                Labels[counter].Height = 23;
                Labels[counter].TextAlign = ContentAlignment.MiddleCenter;

                Buttons[(counter * 3)] = new Button();
                Buttons[(counter * 3)].Left = 555;
                Buttons[(counter * 3)].Top = 5 + (26 * counter);
                Buttons[(counter * 3)].Width = 100;
                Buttons[(counter * 3)].Height = 20;
                Buttons[(counter * 3)].Name = Reader.GetValue(0).ToString() + "InfoA" + counter.ToString();
                Buttons[(counter * 3)].Font = new Font(Buttons[(counter * 3)].Font, FontStyle.Bold);

                Buttons[(1 + (counter * 3))] = new Button();
                Buttons[(1 + (counter * 3))].Left = 661;
                Buttons[(1 + (counter * 3))].Top = 5 + (26 * counter);
                Buttons[(1 + (counter * 3))].Width = 76;
                Buttons[(1 + (counter * 3))].Height = 20;
                Buttons[(1 + (counter * 3))].Font = new Font(Buttons[(1 + (counter * 3))].Font, FontStyle.Bold);
                Buttons[(1 + (counter * 3))].Name = Reader.GetValue(0).ToString();
                Buttons[(1 + (counter * 3))].Text = "Detail Info";
                Buttons[(1 + (counter * 3))].Click +=new EventHandler(detailInfo_Click);

                if (Reader.GetValue(11).ToString() == "True")
                {
                    Buttons[(2 + (counter * 3))] = new Button();
                    Buttons[(2 + (counter * 3))].Left = 745;
                    Buttons[(2 + (counter * 3))].Top = 5 + (26 * counter);
                    Buttons[(2 + (counter * 3))].Width = 20;
                    Buttons[(2 + (counter * 3))].Height = 20;
                    Buttons[(2 + (counter * 3))].Image = Buecherverleih.Properties.Resources.shopping_cart2;
                    Buttons[(2 + (counter * 3))].Name = Reader.GetValue(0).ToString();
                    Buttons[(2 + (counter * 3))].Text = "";
                    Buttons[(2 + (counter * 3))].Click += new EventHandler(erase_reservation_Click);
                    Buttons[(2 + (counter * 3))].MouseEnter += new EventHandler(show_tooltip);
                    Buttons[(2 + (counter * 3))].MouseLeave += new EventHandler(unshow_tooltip);

                    pnl_Search_All_Rows.Controls.Add(Buttons[(2 + (counter * 3))]);
                }

                isborrowed = Convert.ToInt32(Reader.GetValue(8).ToString());
                if (isborrowed == 0)
                {
                    Labels[counter].Text = "Verfügbar";
                    Labels[counter].ForeColor = System.Drawing.Color.Green;

                    Buttons[(counter * 3)].Text = "Ausleihen";
                    Buttons[(counter * 3)].ForeColor = System.Drawing.Color.Green;
                    Buttons[(counter * 3)].Click += new EventHandler(Ausleihen_Click);
                }
                else
                {
                    Labels[counter].Text = "Ausgeliehen";
                    Labels[counter].ForeColor = System.Drawing.Color.Red;

                    Buttons[(counter * 3)].Text = "Zurücknehmen";
                    Buttons[(counter * 3)].ForeColor = System.Drawing.Color.Red;
                    Buttons[(counter * 3)].Click += new EventHandler(Zuruecknehmen_Click);
                }


                pnl_Search_All_Rows.Controls.Add(Textboxes[(counter * 4)]);
                pnl_Search_All_Rows.Controls.Add(Textboxes[(1 + (counter * 4))]);
                pnl_Search_All_Rows.Controls.Add(Textboxes[(2 + (counter * 4))]);
                pnl_Search_All_Rows.Controls.Add(Textboxes[(3 + (counter * 4))]);
                pnl_Search_All_Rows.Controls.Add(Labels[counter]);
                pnl_Search_All_Rows.Controls.Add(Buttons[(counter * 3)]);
                pnl_Search_All_Rows.Controls.Add(Buttons[(1 + (counter * 3))]);

                progressBar_allBooks.PerformStep();

                counter++;

            }
            connection.Close();

            pnl_Search_All_Rows.Focus();
            progressBar_allBooks.Visible = false;

            BringMeToFront = pnl_Search_All;
            FocusMe = pnl_Search_All_Rows;

            
        }

        //Zeigt 50 Bücher an   fängt mit übergebenem Parameter als (bookID - 1) an
        // wird 0 basiert aufgerufen
        public void alleBuecherAnzeigen(int firstBookToShow)
        {
            int allBooksCounted = countAllBooks();

            pnl_Search_All_Rows.Controls.Clear();

            progressBar_allBooks.Visible = true;
            progressBar_allBooks.Refresh();
            progressBar_allBooks.Value = 0;
            progressBar_allBooks.Maximum = 50;
            progressBar_allBooks.Step = 1;
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_books";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            int counter = 0;
            int isborrowed = 2;
            TextBox[] Textboxes = new TextBox[(4 * allBooksCounted)];
            Label[] Labels = new Label[allBooksCounted];
            Button[] Buttons = new Button[(3 * allBooksCounted)];

            while (Reader.Read())
            {
                if ((counter > (firstBookToShow - 1)) && (counter < (firstBookToShow + 50)))
                {
                    Textboxes[(counter * 4)] = new TextBox();
                    Textboxes[(counter * 4)].Text = Reader.GetValue(0).ToString();
                    Textboxes[(counter * 4)].Left = 30;
                    Textboxes[(counter * 4)].Top = 5 + (26 * counter) - (26 * firstBookToShow);
                    Textboxes[(counter * 4)].Width = 25;
                    Textboxes[(counter * 4)].Height = 20;
                    Textboxes[(counter * 4)].ReadOnly = true;

                    Textboxes[(1 + (counter * 4))] = new TextBox();
                    Textboxes[(1 + (counter * 4))].Text = Reader.GetValue(1).ToString();
                    Textboxes[(1 + (counter * 4))].Left = 71;
                    Textboxes[(1 + (counter * 4))].Top = 5 + (26 * counter) - (26 * firstBookToShow);
                    Textboxes[(1 + (counter * 4))].Width = 67;
                    Textboxes[(1 + (counter * 4))].Height = 20;
                    Textboxes[(1 + (counter * 4))].ReadOnly = true;

                    Textboxes[(2 + (counter * 4))] = new TextBox();
                    Textboxes[(2 + (counter * 4))].Text = Reader.GetValue(2).ToString();
                    Textboxes[(2 + (counter * 4))].Left = 165;
                    Textboxes[(2 + (counter * 4))].Top = 5 + (26 * counter) - (26 * firstBookToShow);
                    Textboxes[(2 + (counter * 4))].Width = 86;
                    Textboxes[(2 + (counter * 4))].Height = 20;
                    Textboxes[(2 + (counter * 4))].ReadOnly = true;

                    Textboxes[(3 + (counter * 4))] = new TextBox();
                    Textboxes[(3 + (counter * 4))].Text = Reader.GetValue(3).ToString();
                    Textboxes[(3 + (counter * 4))].Left = 264;
                    Textboxes[(3 + (counter * 4))].Top = 5 + (26 * counter) - (26 * firstBookToShow);
                    Textboxes[(3 + (counter * 4))].Width = 183;
                    Textboxes[(3 + (counter * 4))].Height = 20;
                    Textboxes[(3 + (counter * 4))].ReadOnly = true;

                    Labels[counter] = new Label();
                    Labels[counter].Left = 470;
                    Labels[counter].Top = 5 + (26 * counter) - (26 * firstBookToShow);
                    Labels[counter].Width = 70;
                    Labels[counter].Height = 23;
                    Labels[counter].TextAlign = ContentAlignment.MiddleCenter;

                    Buttons[(counter * 3)] = new Button();
                    Buttons[(counter * 3)].Left = 555;
                    Buttons[(counter * 3)].Top = 5 + (26 * counter) - (26 * firstBookToShow);
                    Buttons[(counter * 3)].Width = 100;
                    Buttons[(counter * 3)].Height = 20;
                    Buttons[(counter * 3)].Name = Reader.GetValue(0).ToString() + "InfoA" + counter.ToString();
                    Buttons[(counter * 3)].Font = new Font(Buttons[(counter * 3)].Font, FontStyle.Bold);

                    Buttons[(1 + (counter * 3))] = new Button();
                    Buttons[(1 + (counter * 3))].Left = 661;
                    Buttons[(1 + (counter * 3))].Top = 5 + (26 * counter) - (26 * firstBookToShow);
                    Buttons[(1 + (counter * 3))].Width = 76;
                    Buttons[(1 + (counter * 3))].Height = 20;
                    Buttons[(1 + (counter * 3))].Font = new Font(Buttons[(1 + (counter * 3))].Font, FontStyle.Bold);
                    Buttons[(1 + (counter * 3))].Name = Reader.GetValue(0).ToString();
                    Buttons[(1 + (counter * 3))].Text = "Detail Info";
                    Buttons[(1 + (counter * 3))].Click += new EventHandler(detailInfo_Click);

                    if (Reader.GetValue(11).ToString() == "True")
                    {
                        Buttons[(2 + (counter * 3))] = new Button();
                        Buttons[(2 + (counter * 3))].Left = 745;
                        Buttons[(2 + (counter * 3))].Top = 5 + (26 * counter);
                        Buttons[(2 + (counter * 3))].Width = 20;
                        Buttons[(2 + (counter * 3))].Height = 20;
                        Buttons[(2 + (counter * 3))].Image = Buecherverleih.Properties.Resources.shopping_cart2;
                        Buttons[(2 + (counter * 3))].Name = Reader.GetValue(0).ToString();
                        Buttons[(2 + (counter * 3))].Text = "";
                        Buttons[(2 + (counter * 3))].Click += new EventHandler(erase_reservation_Click);
                        Buttons[(2 + (counter * 3))].MouseEnter += new EventHandler(show_tooltip);
                        Buttons[(2 + (counter * 3))].MouseLeave += new EventHandler(unshow_tooltip);

                        pnl_Search_All_Rows.Controls.Add(Buttons[(2 + (counter * 3))]);
                    }

                    isborrowed = Convert.ToInt32(Reader.GetValue(8).ToString());
                    if (isborrowed == 0)
                    {
                        Labels[counter].Text = "Verfügbar";
                        Labels[counter].ForeColor = System.Drawing.Color.Green;

                        Buttons[(counter * 3)].Text = "Ausleihen";
                        Buttons[(counter * 3)].ForeColor = System.Drawing.Color.Green;
                        Buttons[(counter * 3)].Click += new EventHandler(Ausleihen_Click);
                    }
                    else
                    {
                        Labels[counter].Text = "Ausgeliehen";
                        Labels[counter].ForeColor = System.Drawing.Color.Red;

                        Buttons[(counter * 3)].Text = "Zurücknehmen";
                        Buttons[(counter * 3)].ForeColor = System.Drawing.Color.Red;
                        Buttons[(counter * 3)].Click += new EventHandler(Zuruecknehmen_Click);
                    }


                    pnl_Search_All_Rows.Controls.Add(Textboxes[(counter * 4)]);
                    pnl_Search_All_Rows.Controls.Add(Textboxes[(1 + (counter * 4))]);
                    pnl_Search_All_Rows.Controls.Add(Textboxes[(2 + (counter * 4))]);
                    pnl_Search_All_Rows.Controls.Add(Textboxes[(3 + (counter * 4))]);
                    pnl_Search_All_Rows.Controls.Add(Labels[counter]);
                    pnl_Search_All_Rows.Controls.Add(Buttons[(counter * 3)]);
                    pnl_Search_All_Rows.Controls.Add(Buttons[(1 + (counter * 3))]);

                    progressBar_allBooks.PerformStep();

                }
                counter++;

            }
            connection.Close();

            pnl_Search_All_Rows.Focus();
            progressBar_allBooks.Visible = false;

            BringMeToFront = pnl_Search_All;
            FocusMe = pnl_Search_All_Rows;
        }

        //Tritt auf beim Verlassen des Buttons zum löschen der Reservierung mit der Maus auf
        void unshow_tooltip(object sender, System.EventArgs e)
        {
            ((Button)sender).Controls.Clear();
        }

        //Tritt auf beim Überfahren des Buttons zum löschen der Reservierung mit der Maus auf
        void show_tooltip(object sender, System.EventArgs e)
        {
            toolTip1.Show("Hebt die Reservierung dieses Buches auf!", ((Button)sender),24,5,4000);
        }

        //Funktion des automatisch generierten Buttons zum löschen der Reservierung
        void erase_reservation_Click(object sender, System.EventArgs e)
        {
            //Dialog, ob Buch wirklich unreservieren
            DialogResult result = MessageBox.Show("Möchten Sie die Reservierung dieses Buches wirklich zurücknehmen?",
                "Bestätigung",
                MessageBoxButtons.OKCancel,
                MessageBoxIcon.Question);

            //Löschen der isborrowed Einträge aus der DB
            if (result == DialogResult.OK)
            {
                string id = ((Button)sender).Name.ToString();

                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                string myInsertQuery = "UPDATE dbbv_books SET reserved=0 WHERE ID = " + id;
                MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                myCommand.Connection = myConnection;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();

                //Bestätigung
                MessageBox.Show("Reservierung wurde zurückgenommen!", "Bestätigung", MessageBoxButtons.OK);

                pnl_Home.BringToFront();
            }
        }

        // startet die Möglichkeit zur Bearbeitung von Ausleihern
        public void borrowerManagement_start()
        {
            pnl_borrowerManagement.BringToFront();
            int allBorrowersAmount = countAllBorrowers();

            pnl_borrowerManagement_main.Controls.Clear();
            progressBar_borrowerManagement.Visible = true;
            progressBar_borrowerManagement.Refresh();
            progressBar_borrowerManagement.Value = 0;
            progressBar_borrowerManagement.Maximum = allBorrowersAmount;
            progressBar_borrowerManagement.Step = 1;
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_borrowers";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            int counter = 0;
            Label[] Labels = new Label[(allBorrowersAmount * 2)];
            Button[] Buttons = new Button[(allBorrowersAmount)];

            while (Reader.Read())
            {
                Labels[(counter * 2)] = new Label();
                Labels[(counter * 2)].Left = 19;
                Labels[(counter * 2)].Top = 26 + (36 * counter);
                Labels[(counter * 2)].Width = 200;
                Labels[(counter * 2)].Height = 23;
                Labels[(counter * 2)].Text = Reader.GetValue(1).ToString();

                Labels[(1 + (counter * 2))] = new Label();
                Labels[(1 + (counter * 2))].Left = 222;
                Labels[(1 + (counter * 2))].Top = 26 + (36 * counter);
                Labels[(1 + (counter * 2))].Width = 200;
                Labels[(1 + (counter * 2))].Height = 23;
                Labels[(1 + (counter * 2))].Text = Reader.GetValue(2).ToString();

                Buttons[(counter)] = new Button();
                Buttons[(counter)].Left = 477;
                Buttons[(counter)].Top = 21 + (36 * counter);
                Buttons[(counter)].Width = 88;
                Buttons[(counter)].Height = 23;
                Buttons[(counter)].Text = "bearbeiten";
                Buttons[(counter)].Name = Reader.GetValue(0).ToString();
                Buttons[(counter)].Font = new Font(Buttons[(counter)].Font, FontStyle.Bold);
                Buttons[(counter)].Click += new EventHandler(borrower_bearbeiten_Click);


                pnl_borrowerManagement_main.Controls.Add(Labels[(counter * 2)]);
                pnl_borrowerManagement_main.Controls.Add(Labels[(1 + (counter * 2))]);
                pnl_borrowerManagement_main.Controls.Add(Buttons[counter]);

                progressBar_borrowerManagement.PerformStep();
                counter++;
            }
            connection.Close();

            pnl_borrowerManagement_main.Focus();
            progressBar_borrowerManagement.Visible = false;
        }

        //Methode, die alle Buchtypen für Bücher zählt und wiedergibt
        public int countAllbooktypes()
        {
            int allbooktypesCounted = 0;

            //Verbindung zur Datenbank aufbauen
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT COUNT(*) FROM dbbv_booktype";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                allbooktypesCounted = Convert.ToInt32(Reader.GetValue(0).ToString());
            }
            connection.Close();

            return allbooktypesCounted;
        }

        //Methode, die alle Aufbewahrungsorte für Bücher zählt und wiedergibt
        public int countAlllocations()
        {
            int allLocationsCounted = 0;

            //Verbindung zur Datenbank aufbauen
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT COUNT(*) FROM dbbv_ort";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                allLocationsCounted = Convert.ToInt32(Reader.GetValue(0).ToString());
            }
            connection.Close();

            return allLocationsCounted;
        }


        //Methode des automatisch generierten Buttons bearbeiten der Aufbewahrungsorte
        void location_bearbeiten_Click(object sender, System.EventArgs e)
        {
            int locationID = Convert.ToInt32(((Button)sender).Name); 
            pnl_location_edit.BringToFront();

            tbox_locationID.Text = locationID.ToString();

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_ort WHERE ID = " + locationID;
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                tbox_locationName.Text = Reader.GetValue(1).ToString();
                lbl_oldlocationName.Text = Reader.GetValue(1).ToString();
            }
            connection.Close();
        }


        //Funktion des automatisch generierten Buttons ausleihen
        void Ausleihen_Click(object sender, System.EventArgs e)
        {
            //Vorname leeren
            tBox_Vorname.Text = "";

            //Auflistung der Combobox hinzufügen
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT borrower_familyname FROM dbbv_borrowers";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            cBox_Nachname.Items.Clear();

            while (Reader.Read())
            {
                cBox_Nachname.Items.Add(Reader.GetValue(0).ToString());
            }
            connection.Close();

            

            //Panel nach vorne holen und Namen des Buttons(die ID aus der DB) einer Var zuweisen
            pnl_borrow.BringToFront();

            //Beispiel für eine Moeglichkeit des HelpStrings: "202InfoA100"
            string HelpString = ((Button)sender).Name;
            //enthält die Zahl vor InfoA
            string ID = HelpString.Substring(0, HelpString.IndexOf("InfoA"));
            //enthält die Zahl nach InfoA
            string restartNumberBookListCall = HelpString.Substring(HelpString.IndexOf("InfoA") + 5,
                (HelpString.Length - (HelpString.IndexOf("InfoA") + 5)));

            //unsichtbarem Label die ID zuweisen
            lbl_DB_ID.Text = ID;
            //unsichtbarem Label2 restartNumberBookListCall zuweisen
            lbl_restartNumberCallBookList.Text = restartNumberBookListCall;

            MySqlConnection connection2 = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command2 = connection2.CreateCommand();
            command2.CommandText = "SELECT * FROM dbbv_books WHERE ID = " + ID;
            MySqlDataReader Reader2;
            connection2.Open();
            Reader2 = command2.ExecuteReader();

            while (Reader2.Read())
            {
                tbox_DB_bookname.Text = Reader2.GetValue(2).ToString();
                tbox_DB_labelID.Text = Reader2.GetValue(1).ToString();
            }
            connection2.Close();
        }

        //Das Zurücknehmen des Buches als Vorgang
        void Zuruecknehmen_Click(object sender, System.EventArgs e)
        {
            //Dialog, ob Buch wirklich zurück
            DialogResult result = MessageBox.Show("Buch wirklich zurücknehmen?\n"+
                "Alle Einträge aus der Datenbank werden gelöscht!\n\n" + 
                "Bitte denken Sie daran das Buch wieder in den Schrank zu stellen!",
                "Bestätigung",
                MessageBoxButtons.OKCancel,
                MessageBoxIcon.Question);

            //Löschen der isborrowed Einträge aus der DB
            if (result == DialogResult.OK)
            {
                //Beispiel für eine Moeglichkeit des HelpStrings: "202InfoA100"
                string HelpString = ((Button)sender).Name;
                //enthält die Zahl vor InfoA
                string bookID = HelpString.Substring(0, HelpString.IndexOf("InfoA"));
                //enthält die Zahl nach InfoA
                string restartNumberBookListCall = HelpString.Substring(HelpString.IndexOf("InfoA") + 5,
                    (HelpString.Length - (HelpString.IndexOf("InfoA") + 5)));

                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                string myDeleteQuery = "UPDATE dbbv_books SET isborrowed = 0, " +
                    "borrowed_by = 0" +
                    ", borrowed_on = '0000-00-00' " +
                    "WHERE ID = " + bookID;
                MySqlCommand myCommand = new MySqlCommand(myDeleteQuery);
                myCommand.Connection = myConnection;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();


                //Rücknahmebestätigung
                MessageBox.Show("Buch wurde zurückgenommen!", "Bestätigung", MessageBoxButtons.OK);

                pnl_Search_All.BringToFront();
                alleBuecherAnzeigen(Convert.ToInt32(restartNumberBookListCall));
                erstelleBuecherMenue();
            }
            
        }


        //Startet die Optionen für Buchtypen
        public void startbooktypes()
        {
            pnl_booktype_Main.Controls.Clear();
            int counter = 0;
            int allbooktypescounted = countAllbooktypes();
            Label[] Labels = new Label[(allbooktypescounted)];
            Button[] Buttons = new Button[(allbooktypescounted)];

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_booktype";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                Labels[counter] = new Label();
                Labels[counter].Left = 19;
                Labels[counter].Top = 26 + (36 * counter);
                Labels[counter].Width = 200;
                Labels[counter].Height = 23;
                Labels[counter].Text = Reader.GetValue(1).ToString();

                Buttons[(counter)] = new Button();
                Buttons[(counter)].Left = 250;
                Buttons[(counter)].Top = 21 + (36 * counter);
                Buttons[(counter)].Width = 88;
                Buttons[(counter)].Height = 23;
                Buttons[(counter)].Text = "bearbeiten";
                Buttons[(counter)].Name = Reader.GetValue(0).ToString();
                Buttons[(counter)].Font = new Font(Buttons[(counter)].Font, FontStyle.Bold);
                Buttons[(counter)].Click += new EventHandler(booktype_bearbeiten_Click);


                pnl_booktype_Main.Controls.Add(Labels[counter]);
                pnl_booktype_Main.Controls.Add(Buttons[counter]);

                counter++;
            }
            connection.Close();

            pnl_booktype_Super.BringToFront();
            pnl_booktype_Main.Focus();
        }


        //Bearbeiten Button des automatisch generierten panels für die Booktypes
        void booktype_bearbeiten_Click(object sender, System.EventArgs e)
        {
            int booktypeID = Convert.ToInt32(((Button)sender).Name);
            pnl_booktypeEdit.BringToFront();

            tbox_booktypeID.Text = booktypeID.ToString();

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_booktype WHERE ID = " + booktypeID;
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                tbox_booktypeName.Text = Reader.GetValue(1).ToString();
                lbl_oldbooktypeName.Text = Reader.GetValue(1).ToString();
            }
            connection.Close();
        }

        //Methode, die alle Aufbewahrungsorte zeigt
        public void startLocation()
        {
            pnl_locations_Main.Controls.Clear();
            int counter = 0;
            int alllocationscounted = countAlllocations();
            Label[] Labels = new Label[(alllocationscounted)];
            Button[] Buttons = new Button[(alllocationscounted)];

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_ort";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                Labels[counter] = new Label();
                Labels[counter].Left = 19;
                Labels[counter].Top = 26 + (36 * counter);
                Labels[counter].Width = 200;
                Labels[counter].Height = 23;
                Labels[counter].Text = Reader.GetValue(1).ToString();

                Buttons[(counter)] = new Button();
                Buttons[(counter)].Left = 250;
                Buttons[(counter)].Top = 21 + (36 * counter);
                Buttons[(counter)].Width = 88;
                Buttons[(counter)].Height = 23;
                Buttons[(counter)].Text = "bearbeiten";
                Buttons[(counter)].Name = Reader.GetValue(0).ToString();
                Buttons[(counter)].Font = new Font(Buttons[(counter)].Font, FontStyle.Bold);
                Buttons[(counter)].Click += new EventHandler(location_bearbeiten_Click);


                pnl_locations_Main.Controls.Add(Labels[counter]);
                pnl_locations_Main.Controls.Add(Buttons[counter]);

                counter++;
            }
            connection.Close();

            pnl_locations_Super.BringToFront();
            pnl_locations_Main.Focus();
        }

        //Ruft die Detailinfos zu einem Buch auf
        void detailInfo_Click(object sender, System.EventArgs e)
        {
            string bookID = ((Button)sender).Name.ToString();
            string ausgeliehen = "";
            string borrowerID = "";
            string borrowedOn = "";

            tbox_book_ID.Text = bookID;

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_books WHERE ID = " + bookID;
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();

            while (Reader.Read())
            {
                tbox_book_labelID.Text = Reader.GetValue(1).ToString();
                tbox_book_name.Text = Reader.GetValue(2).ToString();
                rtBox_book_content.Text = Reader.GetValue(3).ToString();
                tbox_book_type.Text = get_Type_By_ID(Reader.GetValue(4).ToString());
                tbox_book_publisher.Text = Reader.GetValue(5).ToString();
                tbox_book_theme.Text = Reader.GetValue(6).ToString();
                tbox_book_Location.Text = get_Ort_By_ID(Reader.GetValue(7).ToString());
                ausgeliehen = Reader.GetValue(8).ToString();
                borrowerID = Reader.GetValue(9).ToString();
                try
                {
                    borrowedOn = Reader.GetValue(10).ToString();
                    borrowedOn = borrowedOn.Replace("00:00:00", "");
                }
                catch
                {
                    borrowedOn = "00.00.0000";
                }
            }
            connection.Close();

            //Unterscheidung, ob Buch ausgeliehen oder nicht
            if (ausgeliehen == "0")
            {
                //unsichtbar machen der Elemente
                label44.Visible = false;
                label45.Visible = false;
                tbox_book_borrower.Visible = false;
                tbox_book_borrowedOn.Visible = false;

                //Button soll sichtbar sein, wenn das Buch nicht ausgeliehen ist
                btn_eraseBook.Visible = true;
            }
            else
            {
                //sichtbar machen der Elemente
                label44.Visible = true;
                label45.Visible = true;
                tbox_book_borrower.Visible = true;
                tbox_book_borrowedOn.Visible = true;

                //Button soll unsichtbar sein, wenn das Buch ausgeliehen ist
                btn_eraseBook.Visible = false;

                //Zuweisen der Texte an Elemente
                tbox_book_borrowedOn.Text = borrowedOn;

                MySqlConnection connection2 = new MySqlConnection(myConnectionStringGlobal);
                MySqlCommand command2 = connection2.CreateCommand();
                command2.CommandText = "SELECT * FROM dbbv_borrowers WHERE ID = " + borrowerID;
                MySqlDataReader Reader2;
                connection2.Open();
                Reader2 = command2.ExecuteReader();

                while (Reader2.Read())
                {
                    tbox_book_borrower.Text = Reader2.GetValue(1).ToString() + ", " +
                        Reader2.GetValue(2).ToString();
                }
                connection2.Close();
            }


            pnl_bookDetail.BringToFront();
        }


        //Überprüfen, ob LabelID unique ist
        public Boolean checkLabel_isUnique()
        {
            Boolean isUnique = true;
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT labelID FROM dbbv_books";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();

            while (Reader.Read())
            {
                if (textBox2.Text.ToLower() == Reader.GetValue(0).ToString().ToLower())
                {
                    isUnique = false;
                    break;
                }
            }
            connection.Close();

            return isUnique;
        }


        //Alle ausgeliehenen Bücher zählen
        public int countAllBooks_Lend()
        {
            int allBooksLendCounted = 0;
            //Verbindung zur Datenbank aufbauen
            //Methode mit Rückgabewert Alle Bücher zählen aufrufen
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT COUNT(*) FROM dbbv_books WHERE isborrowed = 1";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                allBooksLendCounted = Convert.ToInt32(Reader.GetValue(0).ToString());
            }
            connection.Close();

            return allBooksLendCounted;
        }



        //Methode, die alle Bücher, die von einer bestimmten Person ausgeliehen sind anzeigt
        public void alleBuecherAnzeigen_ausgeliehenVonPerson(string Nachname, string Vorname)
        {
            pnl_SearchPersons_Main.Controls.Clear();

            int PersonID = getIDzuPerson(Nachname, Vorname);

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_books WHERE isborrowed = 1 " +
                "AND borrowed_by = " + PersonID;
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            int counter = 0;
            TextBox[] Textboxes = new TextBox[(4 * 50)];
            Label[] Labels = new Label[50];
            Button[] Buttons = new Button[(3 * 50)];

            while (Reader.Read())
            {
                Textboxes[(counter * 4)] = new TextBox();
                Textboxes[(counter * 4)].Text = Reader.GetValue(0).ToString();
                Textboxes[(counter * 4)].Left = 30;
                Textboxes[(counter * 4)].Top = 5 + (26 * counter);
                Textboxes[(counter * 4)].Width = 25;
                Textboxes[(counter * 4)].Height = 20;
                Textboxes[(counter * 4)].ReadOnly = true;

                Textboxes[(1 + (counter * 4))] = new TextBox();
                Textboxes[(1 + (counter * 4))].Text = Reader.GetValue(1).ToString();
                Textboxes[(1 + (counter * 4))].Left = 71;
                Textboxes[(1 + (counter * 4))].Top = 5 + (26 * counter);
                Textboxes[(1 + (counter * 4))].Width = 67;
                Textboxes[(1 + (counter * 4))].Height = 20;
                Textboxes[(1 + (counter * 4))].ReadOnly = true;

                Textboxes[(2 + (counter * 4))] = new TextBox();
                Textboxes[(2 + (counter * 4))].Text = Reader.GetValue(2).ToString();
                Textboxes[(2 + (counter * 4))].Left = 165;
                Textboxes[(2 + (counter * 4))].Top = 5 + (26 * counter);
                Textboxes[(2 + (counter * 4))].Width = 86;
                Textboxes[(2 + (counter * 4))].Height = 20;
                Textboxes[(2 + (counter * 4))].ReadOnly = true;

                Textboxes[(3 + (counter * 4))] = new TextBox();
                Textboxes[(3 + (counter * 4))].Text = Reader.GetValue(3).ToString();
                Textboxes[(3 + (counter * 4))].Left = 264;
                Textboxes[(3 + (counter * 4))].Top = 5 + (26 * counter);
                Textboxes[(3 + (counter * 4))].Width = 183;
                Textboxes[(3 + (counter * 4))].Height = 20;
                Textboxes[(3 + (counter * 4))].ReadOnly = true;

                Labels[counter] = new Label();
                Labels[counter].Left = 470;
                Labels[counter].Top = 5 + (26 * counter);
                Labels[counter].Width = 70;
                Labels[counter].Height = 23;
                Labels[counter].TextAlign = ContentAlignment.MiddleCenter;

                Buttons[(counter * 3)] = new Button();
                Buttons[(counter * 3)].Left = 555;
                Buttons[(counter * 3)].Top = 5 + (26 * counter);
                Buttons[(counter * 3)].Width = 100;
                Buttons[(counter * 3)].Height = 20;
                Buttons[(counter * 3)].Name = Reader.GetValue(0).ToString();
                Buttons[(counter * 3)].Font = new Font(Buttons[(counter * 3)].Font, FontStyle.Bold);

                Buttons[(1 + (counter * 3))] = new Button();
                Buttons[(1 + (counter * 3))].Left = 661;
                Buttons[(1 + (counter * 3))].Top = 5 + (26 * counter);
                Buttons[(1 + (counter * 3))].Width = 76;
                Buttons[(1 + (counter * 3))].Height = 20;
                Buttons[(1 + (counter * 3))].Font = new Font(Buttons[(1 + (counter * 3))].Font, FontStyle.Bold);
                Buttons[(1 + (counter * 3))].Name = Reader.GetValue(0).ToString();
                Buttons[(1 + (counter * 3))].Text = "Detail Info";
                Buttons[(1 + (counter * 3))].Click += new EventHandler(detailInfo_Click);

                if (Reader.GetValue(11).ToString() == "True")
                {
                    Buttons[(2 + (counter * 3))] = new Button();
                    Buttons[(2 + (counter * 3))].Left = 745;
                    Buttons[(2 + (counter * 3))].Top = 5 + (26 * counter);
                    Buttons[(2 + (counter * 3))].Width = 20;
                    Buttons[(2 + (counter * 3))].Height = 20;
                    Buttons[(2 + (counter * 3))].Image = Buecherverleih.Properties.Resources.shopping_cart2;
                    Buttons[(2 + (counter * 3))].Name = Reader.GetValue(0).ToString();
                    Buttons[(2 + (counter * 3))].Text = "";
                    Buttons[(2 + (counter * 3))].Click += new EventHandler(erase_reservation_Click);
                    Buttons[(2 + (counter * 3))].MouseEnter += new EventHandler(show_tooltip);
                    Buttons[(2 + (counter * 3))].MouseLeave += new EventHandler(unshow_tooltip);

                    pnl_SearchPersons_Main.Controls.Add(Buttons[(2 + (counter * 3))]);
                }

                    Labels[counter].Text = "Ausgeliehen";
                    Labels[counter].ForeColor = System.Drawing.Color.Red;

                    Buttons[(counter * 3)].Text = "Zurücknehmen";
                    Buttons[(counter * 3)].ForeColor = System.Drawing.Color.Red;
                    Buttons[(counter * 3)].Click += new EventHandler(Zuruecknehmen_Click);



                    pnl_SearchPersons_Main.Controls.Add(Textboxes[(counter * 4)]);
                    pnl_SearchPersons_Main.Controls.Add(Textboxes[(1 + (counter * 4))]);
                    pnl_SearchPersons_Main.Controls.Add(Textboxes[(2 + (counter * 4))]);
                    pnl_SearchPersons_Main.Controls.Add(Textboxes[(3 + (counter * 4))]);
                    pnl_SearchPersons_Main.Controls.Add(Labels[counter]);
                    pnl_SearchPersons_Main.Controls.Add(Buttons[(counter * 3)]);
                    pnl_SearchPersons_Main.Controls.Add(Buttons[(1 + (counter * 3))]);

                counter++;

            }
            connection.Close();

            BringMeToFront = pnl_SearchPersons_Super;
            FocusMe = pnl_SearchPersons_Main;

        }



        //Globale Variable salt, die nicht geändert werden darf
        //wird beim Hashen benutzt
        const string salt = "Büch3r53rl31h!";

        //Später bitte SHA512 benutzen



        //löst den Personennamen auf und gibt die ID zurück (Primary Key in borrowers)
        public int getIDzuPerson(string Nachname, string Vorname)
        {
            int ID = 0;

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_borrowers WHERE borrower_familyname = '"
                + Nachname + "' AND borrower_firstname = '" + Vorname + "'";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                ID = Convert.ToInt32(Reader.GetValue(0).ToString());
            }
            connection.Close();

            return ID;
        }

        //fügt dem Panel das Buch des ausgewählten Labels zu
        public void show_book_LabelID(string LabelID)
        {
            pnl_Search_Label_Main.Controls.Clear();

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_books WHERE labelID = '" + LabelID + "'";
            MySqlDataReader Reader;
            connection.Open();
            int isborrowed = 2;
            Reader = command.ExecuteReader();
            TextBox[] Textboxes = new TextBox[4];
            Label[] Labels = new Label[1];
            Button[] Buttons = new Button[3];

            while (Reader.Read())
            {
                Textboxes[0] = new TextBox();
                Textboxes[0].Text = Reader.GetValue(0).ToString();
                Textboxes[0].Left = 30;
                Textboxes[0].Top = 5;
                Textboxes[0].Width = 25;
                Textboxes[0].Height = 20;
                Textboxes[0].ReadOnly = true;

                Textboxes[1] = new TextBox();
                Textboxes[1].Text = Reader.GetValue(1).ToString();
                Textboxes[1].Left = 71;
                Textboxes[1].Top = 5;
                Textboxes[1].Width = 67;
                Textboxes[1].Height = 20;
                Textboxes[1].ReadOnly = true;

                Textboxes[2] = new TextBox();
                Textboxes[2].Text = Reader.GetValue(2).ToString();
                Textboxes[2].Left = 165;
                Textboxes[2].Top = 5;
                Textboxes[2].Width = 86;
                Textboxes[2].Height = 20;
                Textboxes[2].ReadOnly = true;

                Textboxes[3] = new TextBox();
                Textboxes[3].Text = Reader.GetValue(3).ToString();
                Textboxes[3].Left = 264;
                Textboxes[3].Top = 5;
                Textboxes[3].Width = 183;
                Textboxes[3].Height = 20;
                Textboxes[3].ReadOnly = true;

                Labels[0] = new Label();
                Labels[0].Left = 470;
                Labels[0].Top = 5;
                Labels[0].Width = 70;
                Labels[0].Height = 23;
                Labels[0].TextAlign = ContentAlignment.MiddleCenter;

                Buttons[0] = new Button();
                Buttons[0].Left = 555;
                Buttons[0].Top = 5;
                Buttons[0].Width = 100;
                Buttons[0].Height = 20;
                Buttons[0].Name = Reader.GetValue(0).ToString() + "InfoA0";
                Buttons[0].Font = new Font(Buttons[0].Font, FontStyle.Bold);

                Buttons[1] = new Button();
                Buttons[1].Left = 661;
                Buttons[1].Top = 5;
                Buttons[1].Width = 76;
                Buttons[1].Height = 20;
                Buttons[1].Font = new Font(Buttons[1].Font, FontStyle.Bold);
                Buttons[1].Name = Reader.GetValue(0).ToString();
                Buttons[1].Text = "Detail Info";
                Buttons[1].Click += new EventHandler(detailInfo_Click);

                if (Reader.GetValue(11).ToString() == "True")
                {
                    Buttons[2] = new Button();
                    Buttons[2].Left = 745;
                    Buttons[2].Top = 5;
                    Buttons[2].Width = 20;
                    Buttons[2].Height = 20;
                    Buttons[2].Image = Buecherverleih.Properties.Resources.shopping_cart2;
                    Buttons[2].Name = Reader.GetValue(0).ToString();
                    Buttons[2].Text = "";
                    Buttons[2].Click += new EventHandler(erase_reservation_Click);
                    Buttons[2].MouseEnter += new EventHandler(show_tooltip);
                    Buttons[2].MouseLeave += new EventHandler(unshow_tooltip);

                    pnl_Search_Label_Main.Controls.Add(Buttons[2]);
                }

                isborrowed = Convert.ToInt32(Reader.GetValue(8).ToString());
                if (isborrowed == 0)
                {
                    Labels[0].Text = "Verfügbar";
                    Labels[0].ForeColor = System.Drawing.Color.Green;

                    Buttons[0].Text = "Ausleihen";
                    Buttons[0].ForeColor = System.Drawing.Color.Green;
                    Buttons[0].Click += new EventHandler(Ausleihen_Click);
                }
                else
                {
                    Labels[0].Text = "Ausgeliehen";
                    Labels[0].ForeColor = System.Drawing.Color.Red;

                    Buttons[0].Text = "Zurücknehmen";
                    Buttons[0].ForeColor = System.Drawing.Color.Red;
                    Buttons[0].Click += new EventHandler(Zuruecknehmen_Click);
                }



                pnl_Search_Label_Main.Controls.Add(Textboxes[0]);
                pnl_Search_Label_Main.Controls.Add(Textboxes[1]);
                pnl_Search_Label_Main.Controls.Add(Textboxes[2]);
                pnl_Search_Label_Main.Controls.Add(Textboxes[3]);
                pnl_Search_Label_Main.Controls.Add(Labels[0]);
                pnl_Search_Label_Main.Controls.Add(Buttons[0]);
                pnl_Search_Label_Main.Controls.Add(Buttons[1]);

            }
            connection.Close();

            BringMeToFront = pnl_Search_Label_Super;
            FocusMe = pnl_Search_Label_Main;
        }


        //Menü zur Navigierung erstellen
        public void createAllLendBooksMenu()
        {

            int allBooksLend = countAllBooks_Lend();
            int bookscounter = 1;

            pnl_searchAllLend_Menu.Controls.Clear();
            Button[] Buttons = new Button[(allBooksLend)];

            Buttons[0] = new Button();
            Buttons[0].Left = 6;
            Buttons[0].Top = 3;
            Buttons[0].Width = 80;
            Buttons[0].Height = 38;
            Buttons[0].Font = new Font(Buttons[0].Font,
                FontStyle.Bold);
            Buttons[0].Name = "1";
            Buttons[0].Text = "Alle anzeigen";
            Buttons[0].Click += new EventHandler(show_BooksLend_Click);

            pnl_searchAllLend_Menu.Controls.Add(Buttons[0]);

            while ((bookscounter * 50) < (allBooksLend + 50))
            {
                Buttons[(bookscounter + 1)] = new Button();
                Buttons[(bookscounter + 1)].Left = 31 + (61 * bookscounter);
                Buttons[(bookscounter + 1)].Top = 5;
                Buttons[(bookscounter + 1)].Width = 54;
                Buttons[(bookscounter + 1)].Height = 35;
                Buttons[(bookscounter + 1)].Font = new Font(Buttons[(bookscounter + 1)].Font,
                    FontStyle.Bold);
                Buttons[(bookscounter + 1)].Name = (bookscounter * 50).ToString();
                Buttons[(bookscounter + 1)].Text = bookscounter.ToString();
                Buttons[(bookscounter + 1)].Click += new EventHandler(show_BooksLend_Click);


                pnl_searchAllLend_Menu.Controls.Add(Buttons[(bookscounter + 1)]);

                bookscounter++;
            }
        }

        //Click Event der dynamisch generierten Buttons der Suche nach allen ausgeliehenen Büchern 
        void show_BooksLend_Click(object sender, System.EventArgs e)
        {
            int bookswitch = Convert.ToInt32(((Button)sender).Name.ToString());

            if (bookswitch == 1)
            {
                showAllLendBooks();
            }
            else
            {
                showAllLendBooks((bookswitch - 50));
            }

        }

        //Alle ausgeliehen Bücher auf einmal anzeigen
        public void showAllLendBooks()
        {
            int allLendBooksCounted = countAllBooks_Lend();

            pnl_searchAllLend_Main.Controls.Clear();
            progressBar_LendBooks.Visible = true;
            progressBar_LendBooks.Refresh();
            progressBar_LendBooks.Value = 0;
            progressBar_LendBooks.Maximum = allLendBooksCounted;
            progressBar_LendBooks.Step = 1;
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_books WHERE isborrowed = 1";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            int counter = 0;
            TextBox[] Textboxes = new TextBox[(4 * allLendBooksCounted)];
            Label[] Labels = new Label[allLendBooksCounted];
            Button[] Buttons = new Button[(3 * allLendBooksCounted)];

            while (Reader.Read())
            {
                Textboxes[(counter * 4)] = new TextBox();
                Textboxes[(counter * 4)].Text = Reader.GetValue(0).ToString();
                Textboxes[(counter * 4)].Left = 30;
                Textboxes[(counter * 4)].Top = 5 + (26 * counter);
                Textboxes[(counter * 4)].Width = 25;
                Textboxes[(counter * 4)].Height = 20;
                Textboxes[(counter * 4)].ReadOnly = true;

                Textboxes[(1 + (counter * 4))] = new TextBox();
                Textboxes[(1 + (counter * 4))].Text = Reader.GetValue(1).ToString();
                Textboxes[(1 + (counter * 4))].Left = 71;
                Textboxes[(1 + (counter * 4))].Top = 5 + (26 * counter);
                Textboxes[(1 + (counter * 4))].Width = 67;
                Textboxes[(1 + (counter * 4))].Height = 20;
                Textboxes[(1 + (counter * 4))].ReadOnly = true;

                Textboxes[(2 + (counter * 4))] = new TextBox();
                Textboxes[(2 + (counter * 4))].Text = Reader.GetValue(2).ToString();
                Textboxes[(2 + (counter * 4))].Left = 165;
                Textboxes[(2 + (counter * 4))].Top = 5 + (26 * counter);
                Textboxes[(2 + (counter * 4))].Width = 86;
                Textboxes[(2 + (counter * 4))].Height = 20;
                Textboxes[(2 + (counter * 4))].ReadOnly = true;

                Textboxes[(3 + (counter * 4))] = new TextBox();
                Textboxes[(3 + (counter * 4))].Text = Reader.GetValue(3).ToString();
                Textboxes[(3 + (counter * 4))].Left = 264;
                Textboxes[(3 + (counter * 4))].Top = 5 + (26 * counter);
                Textboxes[(3 + (counter * 4))].Width = 183;
                Textboxes[(3 + (counter * 4))].Height = 20;
                Textboxes[(3 + (counter * 4))].ReadOnly = true;

                Labels[counter] = new Label();
                Labels[counter].Left = 470;
                Labels[counter].Top = 5 + (26 * counter);
                Labels[counter].Width = 70;
                Labels[counter].Height = 23;
                Labels[counter].TextAlign = ContentAlignment.MiddleCenter;

                Buttons[(counter * 3)] = new Button();
                Buttons[(counter * 3)].Left = 555;
                Buttons[(counter * 3)].Top = 5 + (26 * counter);
                Buttons[(counter * 3)].Width = 100;
                Buttons[(counter * 3)].Height = 20;
                Buttons[(counter * 3)].Name = Reader.GetValue(0).ToString() + "InfoA" + counter.ToString();
                Buttons[(counter * 3)].Font = new Font(Buttons[(counter * 3)].Font, FontStyle.Bold);

                Buttons[(1 + (counter * 3))] = new Button();
                Buttons[(1 + (counter * 3))].Left = 661;
                Buttons[(1 + (counter * 3))].Top = 5 + (26 * counter);
                Buttons[(1 + (counter * 3))].Width = 76;
                Buttons[(1 + (counter * 3))].Height = 20;
                Buttons[(1 + (counter * 3))].Font = new Font(Buttons[(1 + (counter * 3))].Font, FontStyle.Bold);
                Buttons[(1 + (counter * 3))].Name = Reader.GetValue(0).ToString();
                Buttons[(1 + (counter * 3))].Text = "Detail Info";
                Buttons[(1 + (counter * 3))].Click += new EventHandler(detailInfo_Click);

                if (Reader.GetValue(11).ToString() == "True")
                {
                    Buttons[(2 + (counter * 3))] = new Button();
                    Buttons[(2 + (counter * 3))].Left = 745;
                    Buttons[(2 + (counter * 3))].Top = 5 + (26 * counter);
                    Buttons[(2 + (counter * 3))].Width = 20;
                    Buttons[(2 + (counter * 3))].Height = 20;
                    Buttons[(2 + (counter * 3))].Image = Buecherverleih.Properties.Resources.shopping_cart2;
                    Buttons[(2 + (counter * 3))].Name = Reader.GetValue(0).ToString();
                    Buttons[(2 + (counter * 3))].Text = "";
                    Buttons[(2 + (counter * 3))].Click += new EventHandler(erase_reservation_Click);
                    Buttons[(2 + (counter * 3))].MouseEnter += new EventHandler(show_tooltip);
                    Buttons[(2 + (counter * 3))].MouseLeave += new EventHandler(unshow_tooltip);

                    pnl_searchAllLend_Main.Controls.Add(Buttons[(2 + (counter * 3))]);
                }


                    Labels[counter].Text = "Ausgeliehen";
                    Labels[counter].ForeColor = System.Drawing.Color.Red;

                    Buttons[(counter * 3)].Text = "Zurücknehmen";
                    Buttons[(counter * 3)].ForeColor = System.Drawing.Color.Red;
                    Buttons[(counter * 3)].Click += new EventHandler(Zuruecknehmen_Click);


                pnl_searchAllLend_Main.Controls.Add(Textboxes[(counter * 4)]);
                pnl_searchAllLend_Main.Controls.Add(Textboxes[(1 + (counter * 4))]);
                pnl_searchAllLend_Main.Controls.Add(Textboxes[(2 + (counter * 4))]);
                pnl_searchAllLend_Main.Controls.Add(Textboxes[(3 + (counter * 4))]);
                pnl_searchAllLend_Main.Controls.Add(Labels[counter]);
                pnl_searchAllLend_Main.Controls.Add(Buttons[(counter * 3)]);
                pnl_searchAllLend_Main.Controls.Add(Buttons[(1 + (counter * 3))]);

                progressBar_LendBooks.PerformStep();

                counter++;

            }
            connection.Close();

            progressBar_LendBooks.Visible = false;

            BringMeToFront = pnl_searchAllLend;
            FocusMe = pnl_searchAllLend_Main;
        }



        //Ruft 50 Bücher aller ausgeliehenen Bücher auf
        public void showAllLendBooks(int firstBookToShow)
        {
            int allLendBooksCounted = countAllBooks_Lend();

            pnl_searchAllLend_Main.Controls.Clear();
            progressBar_LendBooks.Visible = true;
            progressBar_LendBooks.Refresh();
            progressBar_LendBooks.Value = 0;
            progressBar_LendBooks.Maximum = allLendBooksCounted;
            progressBar_LendBooks.Step = 1;
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_books WHERE isborrowed = 1";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            int counter = 0;
            TextBox[] Textboxes = new TextBox[(4 * allLendBooksCounted)];
            Label[] Labels = new Label[allLendBooksCounted];
            Button[] Buttons = new Button[(3 * allLendBooksCounted)];

            while (Reader.Read())
            {

                if ((counter > (firstBookToShow - 1)) && (counter < (firstBookToShow + 50)))
                {
                    Textboxes[(counter * 4)] = new TextBox();
                    Textboxes[(counter * 4)].Text = Reader.GetValue(0).ToString();
                    Textboxes[(counter * 4)].Left = 30;
                    Textboxes[(counter * 4)].Top = 5 + (26 * counter);
                    Textboxes[(counter * 4)].Width = 25;
                    Textboxes[(counter * 4)].Height = 20;
                    Textboxes[(counter * 4)].ReadOnly = true;

                    Textboxes[(1 + (counter * 4))] = new TextBox();
                    Textboxes[(1 + (counter * 4))].Text = Reader.GetValue(1).ToString();
                    Textboxes[(1 + (counter * 4))].Left = 71;
                    Textboxes[(1 + (counter * 4))].Top = 5 + (26 * counter);
                    Textboxes[(1 + (counter * 4))].Width = 67;
                    Textboxes[(1 + (counter * 4))].Height = 20;
                    Textboxes[(1 + (counter * 4))].ReadOnly = true;

                    Textboxes[(2 + (counter * 4))] = new TextBox();
                    Textboxes[(2 + (counter * 4))].Text = Reader.GetValue(2).ToString();
                    Textboxes[(2 + (counter * 4))].Left = 165;
                    Textboxes[(2 + (counter * 4))].Top = 5 + (26 * counter);
                    Textboxes[(2 + (counter * 4))].Width = 86;
                    Textboxes[(2 + (counter * 4))].Height = 20;
                    Textboxes[(2 + (counter * 4))].ReadOnly = true;

                    Textboxes[(3 + (counter * 4))] = new TextBox();
                    Textboxes[(3 + (counter * 4))].Text = Reader.GetValue(3).ToString();
                    Textboxes[(3 + (counter * 4))].Left = 264;
                    Textboxes[(3 + (counter * 4))].Top = 5 + (26 * counter);
                    Textboxes[(3 + (counter * 4))].Width = 183;
                    Textboxes[(3 + (counter * 4))].Height = 20;
                    Textboxes[(3 + (counter * 4))].ReadOnly = true;

                    Labels[counter] = new Label();
                    Labels[counter].Left = 470;
                    Labels[counter].Top = 5 + (26 * counter);
                    Labels[counter].Width = 70;
                    Labels[counter].Height = 23;
                    Labels[counter].TextAlign = ContentAlignment.MiddleCenter;

                    Buttons[(counter * 3)] = new Button();
                    Buttons[(counter * 3)].Left = 555;
                    Buttons[(counter * 3)].Top = 5 + (26 * counter);
                    Buttons[(counter * 3)].Width = 100;
                    Buttons[(counter * 3)].Height = 20;
                    Buttons[(counter * 3)].Name = Reader.GetValue(0).ToString() + "InfoA" + counter.ToString();
                    Buttons[(counter * 3)].Font = new Font(Buttons[(counter * 3)].Font, FontStyle.Bold);

                    Buttons[(1 + (counter * 3))] = new Button();
                    Buttons[(1 + (counter * 3))].Left = 661;
                    Buttons[(1 + (counter * 3))].Top = 5 + (26 * counter);
                    Buttons[(1 + (counter * 3))].Width = 76;
                    Buttons[(1 + (counter * 3))].Height = 20;
                    Buttons[(1 + (counter * 3))].Font = new Font(Buttons[(1 + (counter * 3))].Font, FontStyle.Bold);
                    Buttons[(1 + (counter * 3))].Name = Reader.GetValue(0).ToString();
                    Buttons[(1 + (counter * 3))].Text = "Detail Info";
                    Buttons[(1 + (counter * 3))].Click += new EventHandler(detailInfo_Click);


                    if (Reader.GetValue(11).ToString() == "True")
                    {
                        Buttons[(2 + (counter * 3))] = new Button();
                        Buttons[(2 + (counter * 3))].Left = 745;
                        Buttons[(2 + (counter * 3))].Top = 5 + (26 * counter);
                        Buttons[(2 + (counter * 3))].Width = 20;
                        Buttons[(2 + (counter * 3))].Height = 20;
                        Buttons[(2 + (counter * 3))].Image = Buecherverleih.Properties.Resources.shopping_cart2;
                        Buttons[(2 + (counter * 3))].Name = Reader.GetValue(0).ToString();
                        Buttons[(2 + (counter * 3))].Text = "";
                        Buttons[(2 + (counter * 3))].Click += new EventHandler(erase_reservation_Click);
                        Buttons[(2 + (counter * 3))].MouseEnter += new EventHandler(show_tooltip);
                        Buttons[(2 + (counter * 3))].MouseLeave += new EventHandler(unshow_tooltip);

                        pnl_searchAllLend_Main.Controls.Add(Buttons[(2 + (counter * 3))]);
                    }


                    Labels[counter].Text = "Ausgeliehen";
                    Labels[counter].ForeColor = System.Drawing.Color.Red;

                    Buttons[(counter * 3)].Text = "Zurücknehmen";
                    Buttons[(counter * 3)].ForeColor = System.Drawing.Color.Red;
                    Buttons[(counter * 3)].Click += new EventHandler(Zuruecknehmen_Click);


                    pnl_searchAllLend_Main.Controls.Add(Textboxes[(counter * 4)]);
                    pnl_searchAllLend_Main.Controls.Add(Textboxes[(1 + (counter * 4))]);
                    pnl_searchAllLend_Main.Controls.Add(Textboxes[(2 + (counter * 4))]);
                    pnl_searchAllLend_Main.Controls.Add(Textboxes[(3 + (counter * 4))]);
                    pnl_searchAllLend_Main.Controls.Add(Labels[counter]);
                    pnl_searchAllLend_Main.Controls.Add(Buttons[(counter * 3)]);
                    pnl_searchAllLend_Main.Controls.Add(Buttons[(1 + (counter * 3))]);

                    progressBar_LendBooks.PerformStep();

                }
                counter++;

            }
            connection.Close();

            progressBar_LendBooks.Visible = false;

            BringMeToFront = pnl_searchAllLend;
            FocusMe = pnl_searchAllLend_Main;

        }

        public void login()
        {
            if (tbox_username.Text == "" || tbox_password.Text == "")
            {
                MessageBox.Show("Bitte Benutzername und Passwort eingeben!",
                    "Eingaben komplettieren",
                    MessageBoxButtons.OK, 
                    MessageBoxIcon.Warning);
            }
            else
            {
                bool userexists = false;
                MySqlConnection connection2 = new MySqlConnection(myConnectionStringGlobal);
                MySqlCommand command2 = connection2.CreateCommand();
                command2.CommandText = "SELECT Count(password) FROM dbbv_users WHERE username = '" +
                    tbox_username.Text + "'";
                MySqlDataReader Reader2;
                connection2.Open();
                Reader2 = command2.ExecuteReader();
                while (Reader2.Read())
                {
                    if (Convert.ToInt32(Reader2.GetValue(0).ToString()) == 1)
                    {
                        userexists = true;
                    }
                }
                connection2.Close();

                if (userexists)
                {
                    bool pwiscorrect = false;
                    MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
                    MySqlCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT password FROM dbbv_users WHERE username = '" +
                        tbox_username.Text + "'";
                    MySqlDataReader Reader;
                    connection.Open();
                    Reader = command.ExecuteReader();
                    while (Reader.Read())
                    {
                        System.Security.Cryptography.SHA512 shaM = System.Security.Cryptography.SHA512.Create();

                        if (BitConverter.ToString(
                            shaM.ComputeHash(
                            Encoding.Unicode.GetBytes((
                            tbox_password.Text + 
                            salt + 
                            tbox_username.Text)))).Replace("-","").ToLower() == Reader.GetValue(0).ToString())
                        {
                            pwiscorrect = true;
                        }
                    }
                    connection.Close();

                    if (pwiscorrect)
                    {
                        if (tbox_password.Text == "123")
                        {
                            pwisdefault();
                            pnl_login.BackgroundImage = null;
                            pnl_header.BringToFront();
                        }
                        else
                        {
                            welcome();
                            pnl_login.BackgroundImage = null;
                            pnl_header.BringToFront();
                        }

                    }
                    else
                    {
                        lbl_loginerror.Text = "Benutzername und/oder Passwort sind falsch!";
                        tbox_password.Text = "";
                    }

                }
                else
                {
                    lbl_loginerror.Text = "Benutzername und/oder Passwort sind falsch!";
                    tbox_password.Text = "";
                }
            }
        }


        //Methode, die bei korrektem PW ausgeführt wird, das nicht dem Default Wert entspricht
        public void welcome()
        {
            //Herausfinden des Vornamens des Benutzers
            string welcomename = "";
            MySqlConnection connection3 = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command3 = connection3.CreateCommand();
            command3.CommandText = "SELECT user_firstname FROM dbbv_users WHERE username = '" +
                tbox_username.Text + "'";
            MySqlDataReader Reader3;
            connection3.Open();
            Reader3 = command3.ExecuteReader();
            while (Reader3.Read())
            {
                welcomename = Reader3.GetValue(0).ToString();
            }
            connection3.Close();

            //Anhand Länge des Vornames Position des Labels bestimmen
            lbl_welcomeName.Text = welcomename;
            lbl_welcomeName.Left -= (welcomename.Length * 7);

            pnl_Home.BringToFront();
            pnl_Menu_Bottom.BringToFront();
        }


        //Methode, die bei korrektem PW ausgeführt wird, das aber noch dem Default Wert entspricht
        public void pwisdefault()
        {
            tbox_ChangeDefaultpwUsername.Text = tbox_username.Text;
            pnl_changedefaultpw.BringToFront();
            tbox_ChangeDefaultpwPassword.Focus();
        }


        //Methode, die aufgerufen wird, wenn das default PW geändert werden soll
        public void changedefaultpw()
        {
            //Check, ob in beiden Feldern was drin ist
            if ((stringisempty(tbox_ChangeDefaultpwPassword.Text)) || (stringisempty(tbox_ChangeDefaultpwconfirmpw.Text)))
            {
                //Wenn nicht Fehlermeldung
                MessageBox.Show("Bitte tragen Sie in beide Felder etwas ein!",
                    "Fehler",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
            }
            else
            {
                //Check, ob der Text beider Felder identisch ist
                if (!stringsarethesame(tbox_ChangeDefaultpwPassword.Text, tbox_ChangeDefaultpwconfirmpw.Text))
                {
                    //Wenn nicht Fehlermeldung
                    MessageBox.Show("Die Passwörter sind nicht identisch!", "Fehler", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    //Beiden Feldern Text löschen
                    tbox_ChangeDefaultpwconfirmpw.Text = "";
                    tbox_ChangeDefaultpwPassword.Text = "";
                    tbox_ChangeDefaultpwPassword.Focus();
                }
                else
                {   
                    //Check, ob PWRules beachtet wurden
                    //Check ob min 8 Zeichen
                    if (tbox_ChangeDefaultpwPassword.Text.Length < 8)
                    {
                        //Wenn nicht Fehlermeldung
                        MessageBox.Show("Das Passwort muss mindestens 8 Zeichen enthalten!", "Fehler", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        //Beiden Feldern Text löschen
                        tbox_ChangeDefaultpwconfirmpw.Text = "";
                        tbox_ChangeDefaultpwPassword.Text = "";
                        tbox_ChangeDefaultpwPassword.Focus();
                    }
                    else
                    {
                        char wrongchar = passwordcontainsunallowedchar(tbox_ChangeDefaultpwPassword.Text);
                        //Check auf unzulässige Zeichen
                        if (wrongchar != 'a')
                        {
                            //Wenn nicht Fehlermeldung
                            MessageBox.Show("Das Passwort enthält folgendes unzulässiges Zeichen:  " + wrongchar.ToString(), "Fehler", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            //Beiden Feldern Text löschen
                            tbox_ChangeDefaultpwconfirmpw.Text = "";
                            tbox_ChangeDefaultpwPassword.Text = "";
                            tbox_ChangeDefaultpwPassword.Focus();
                        }
                        else
                        {
                            if (!passworldrulesareaccomplished(tbox_ChangeDefaultpwPassword.Text)) //Check, ob string 1 Buchstabe, 1 Zahl, 1 Sonderzeichen
                            {
                                //Wenn nicht Fehlermeldung und Label mit Regeln Rot markieren und fett machen
                                lbl_DefaultPWRules.Font = new Font(lbl_DefaultPWRules.Font, FontStyle.Bold);
                                lbl_DefaultPWRules.ForeColor = Color.Red;
                                MessageBox.Show("Bitte beachten Sie die Regeln für das " +
                                    "Erstellen eines Passwortes!\n(Jetzt rot und fett markiert)"
                                    , "Fehler", MessageBoxButtons.OK
                                    , MessageBoxIcon.Error);
                                //Beiden Feldern Text löschen
                                tbox_ChangeDefaultpwconfirmpw.Text = "";
                                tbox_ChangeDefaultpwPassword.Text = "";
                                tbox_ChangeDefaultpwPassword.Focus();
                            }
                            else
                            {
                                //PW salzen, hashen und in DB speichern
                                string newpw = tbox_ChangeDefaultpwPassword.Text + salt + tbox_username.Text;
                                System.Security.Cryptography.SHA512 shaM = System.Security.Cryptography.SHA512.Create();

                                newpw = BitConverter.ToString(
                                            shaM.ComputeHash(
                                            Encoding.Unicode.GetBytes((
                                            newpw)))).Replace("-", "").ToLower();

                                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                                string myInsertQuery = "UPDATE dbbv_users SET password = '" + 
                                    newpw + "' WHERE username = '" + tbox_username.Text + "'";
                                MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                                myCommand.Connection = myConnection;
                                myConnection.Open();
                                myCommand.ExecuteNonQuery();
                                myCommand.Connection.Close();

                                //Bestätigungsmeldung über erfolgreichen PWChange
                                MessageBox.Show("Ihr Passwort wurde erfolgreich geändert!",
                                    "Bestätigung",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Information);

                                //auf Hauptpanel leiten
                                welcome();
                            }
                        }
                    }
                }
              
            }
            
        }


        //Methode, die aufgerufen wird, wenn das PW geändert werden soll
        public void ChangePw()
        {
            if (tbox_ChangePW_OldPW.Text == "" ||
                tbox_changePW_NewPW.Text == "" ||
                tbox_ChangePW_ConfirmNewPW.Text == "")
            {
                MessageBox.Show("Bitte in alle 3 Felder etwas eintragen!",
                    "Eingaben komplettieren",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Warning);
            }
            else
            {
                bool pwiscorrect = false;
                MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT password FROM dbbv_users WHERE username = '" +
                    tbox_username.Text + "'";
                MySqlDataReader Reader;
                connection.Open();
                Reader = command.ExecuteReader();
                while (Reader.Read())
                {
                    System.Security.Cryptography.SHA512 shaM = System.Security.Cryptography.SHA512.Create();

                    if (BitConverter.ToString(
                        shaM.ComputeHash(
                        Encoding.Unicode.GetBytes((
                        tbox_ChangePW_OldPW.Text +
                        salt +
                        tbox_username.Text)))).Replace("-", "").ToLower() == Reader.GetValue(0).ToString())
                    {
                        pwiscorrect = true;
                    }
                }
                connection.Close();

                if (pwiscorrect)
                {
                    //Check, ob der Text beider Felder identisch ist
                    if (!stringsarethesame(tbox_changePW_NewPW.Text, tbox_ChangePW_ConfirmNewPW.Text))
                    {
                        //Wenn nicht Fehlermeldung
                        MessageBox.Show("Die Passwörter sind nicht identisch!",
                            "Fehler",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Error);
                        //3 Feldern Text löschen
                        tbox_ChangePW_ConfirmNewPW.Text = "";
                        tbox_changePW_NewPW.Text = "";
                        tbox_ChangePW_OldPW.Text = "";
                        tbox_ChangePW_OldPW.Focus();
                    }
                    else
                    {
                        //Check, ob PWRules beachtet wurden
                        //Check ob min 8 Zeichen
                        if (tbox_changePW_NewPW.Text.Length < 8)
                        {
                            //Wenn nicht Fehlermeldung
                            MessageBox.Show("Das Passwort muss mindestens 8 Zeichen enthalten!", 
                                "Fehler", 
                                MessageBoxButtons.OK, 
                                MessageBoxIcon.Error);
                            //3 Feldern Text löschen
                            tbox_ChangePW_ConfirmNewPW.Text = "";
                            tbox_changePW_NewPW.Text = "";
                            tbox_ChangePW_OldPW.Text = "";
                            tbox_ChangePW_OldPW.Focus();
                        }
                        else
                        {
                            char wrongchar = passwordcontainsunallowedchar(tbox_changePW_NewPW.Text);
                            //Check auf unzulässige Zeichen
                            if (wrongchar != 'a')
                            {
                                //Wenn nicht Fehlermeldung
                                MessageBox.Show("Das Passwort enthält folgendes unzulässiges Zeichen:  " + 
                                    wrongchar.ToString(), 
                                    "Fehler", 
                                    MessageBoxButtons.OK, 
                                    MessageBoxIcon.Error);

                                //3 Feldern Text löschen
                                tbox_ChangePW_ConfirmNewPW.Text = "";
                                tbox_changePW_NewPW.Text = "";
                                tbox_ChangePW_OldPW.Text = "";
                                tbox_ChangePW_OldPW.Focus();
                            }
                            else
                            {
                                if (!passworldrulesareaccomplished(tbox_changePW_NewPW.Text)) //Check, ob string 1 Buchstabe, 1 Zahl, 1 Sonderzeichen
                                {
                                    //Wenn nicht Fehlermeldung und Label mit Regeln Rot markieren und fett machen
                                    lbl_PWRules.Font = new Font(lbl_PWRules.Font, FontStyle.Bold);
                                    lbl_PWRules.ForeColor = Color.Red;
                                    MessageBox.Show("Bitte beachten Sie die Regeln für das " +
                                        "Erstellen eines Passwortes!\n(Jetzt rot und fett markiert)", 
                                        "Fehler", 
                                        MessageBoxButtons.OK,                                         
                                        MessageBoxIcon.Error);

                                    //3 Feldern Text löschen
                                    tbox_ChangePW_ConfirmNewPW.Text = "";
                                    tbox_changePW_NewPW.Text = "";
                                    tbox_ChangePW_OldPW.Text = "";
                                    tbox_ChangePW_OldPW.Focus();
                                }
                                else
                                {
                                    //PW salzen, hashen und in DB speichern
                                    string newpw = tbox_changePW_NewPW.Text + salt + tbox_username.Text;
                                    System.Security.Cryptography.SHA512 shaM = System.Security.Cryptography.SHA512.Create();

                                    newpw = BitConverter.ToString(
                                                shaM.ComputeHash(
                                                Encoding.Unicode.GetBytes((
                                                newpw)))).Replace("-", "").ToLower();

                                    MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                                    string myInsertQuery = "UPDATE dbbv_users SET password = '" +
                                        newpw + "' WHERE username = '" + tbox_username.Text + "'";
                                    MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                                    myCommand.Connection = myConnection;
                                    myConnection.Open();
                                    myCommand.ExecuteNonQuery();
                                    myCommand.Connection.Close();

                                    //Bestätigungsmeldung über erfolgreichen PWChange
                                    MessageBox.Show("Ihr Passwort wurde erfolgreich geändert!",
                                        "Bestätigung",
                                        MessageBoxButtons.OK,
                                        MessageBoxIcon.Information);

                                    //Auf Hauptpanel leiten
                                    pnl_Home.BringToFront();
                                    pnl_Menu_Bottom.BringToFront();
                                }
                            }
                        }
                    }
                }
                else
                {
                    MessageBox.Show("Ihr altes Passwort ist falsch!",
                        "Eingaben überprüfen",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Error);


                    tbox_ChangePW_OldPW.Text = "";
                    tbox_changePW_NewPW.Text = "";
                    tbox_ChangePW_ConfirmNewPW.Text = "";
                }
            }
        }



        //Methode, die überprüft, ob ein Passwort min. 1 Buchstabe (A-Z,a-z), 
        //min. 1 Zahl und min 1 Sonderzeichen hat
        //Diese Regeln sind auch im Programm als Labeltext zu finden
        public bool passworldrulesareaccomplished(string testMe)
        {
            bool returnMe = false;
            bool specialsignexists = false;
            bool numberexists = false;
            bool letterexists = false;

            for (int i = 33; i < 127;i++ )//Sonderzeichen
            {
                if (i == 33 || i == 34 || i == 35 || i == 36 || i == 37 || i == 40 || i == 41
                     || i == 43 || i == 44 || i == 45 || i == 46 || i == 47 || i == 58
                     || i == 59 || i == 61 || i == 63 || i == 64 || i == 91 || i == 93
                     || i == 95 || i == 123 || i == 124 || i == 125 || i == 126)
                {
                    if (testMe.Contains(char.ConvertFromUtf32(i)))
                    {
                        specialsignexists = true;
                        break;
                    }
                }
            }

            if (specialsignexists)
            {
                for (int i = 48; i < 58; i++)//Zahlen
                {
                    if (testMe.Contains(char.ConvertFromUtf32(i)))
                    {
                        numberexists = true;
                        break;
                    }
                }

                if (numberexists)
                {
                    for (int i = 65; i < 123; i++)//Buchstaben
                    {
                        if (i > 90 && i < 97)
                        {
                            continue;
                        }
                        if (testMe.Contains(char.ConvertFromUtf32(i)))
                        {
                            letterexists = true;
                            break;
                        }
                    }
                }
            }

            returnMe = (numberexists && specialsignexists && letterexists);

            return returnMe;
        }


        //Methode, die überprüft, ob unerlaubte Zeichen im Passwort enthalten sind
        public char passwordcontainsunallowedchar(string testMe)
        {
            char returnME = 'a';
            for (int i = 0; i < 65536; i++)
            {
                if (i > 32 && i < 126)
                {
                    if (i == 38 || i == 39 || i == 42 || i == 60 || i == 62 || i == 92 || i == 94 || i == 96)
                    {
                        if (testMe.Contains(char.ConvertFromUtf32(i)))
                        {
                            returnME = Convert.ToChar(char.ConvertFromUtf32(i));
                            break;
                        }
                    }
                    else
                    {
                        continue;
                    }
                }
                else
                {
                    if (i > 55295 && i < 57344)
                    {
                        continue;
                    }

                    if (testMe.Contains(char.ConvertFromUtf32(i)))
                    {
                        returnME = Convert.ToChar(char.ConvertFromUtf32(i));
                        break;
                    }
                }
            }
            return returnME;
        }

        //Erstellen des Benutzernamens
        //Beim erstellen eines neuen Benutzers 
        //muss ein eindeutiger Benutzername gewährleistet werden
        public void CreateUsername()
        {
            //Testen, ob bei Vor- und Nachname mindestens 2 Zeichen sind
            if (tbox_newUser_Nachname.Text.Length < 3 || tbox_newUser_Vorname.Text.Length < 3)
            {
                MessageBox.Show("Sowohl Nachname, als auch Vorname müssen mindestens 2 Zeichen sein!",
                    "Eingaben komplettieren",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Warning);
            }
            else
            {
                //Benutzernamen generieren

                bool usernameIsUnique = false;
                int counterLetter = 1;
                int counterNumber = 1;
                string Username = "";

                //WhileSchleife Bis Name Unique
                while (!usernameIsUnique)
                {
                    Username = "";
                    if (counterLetter > tbox_newUser_Vorname.Text.Length)
                    {
                        //Wenn keine Buchstaben mehr im Vornamen, dann Zahlen
                        Username = tbox_newUser_Nachname.Text + 
                            tbox_newUser_Vorname.Text + 
                            counterNumber.ToString();
                        counterNumber++;
                    }
                    else
                    {
                        //Nachname + x.Buchstabe Vorname
                        Username = tbox_newUser_Nachname.Text;
                        Username += tbox_newUser_Vorname.Text.Substring(0, counterLetter);
                        counterLetter++;
                    }
                    usernameIsUnique = Check_Username_Unique(Username.ToLower());
                }
                //Benutzername auf tbox schreiben
                tbox_newUser_Username.Text = Username.ToLower();
                btn_newUser_Save.Focus();
            }
        }

        //Prüft ob Username einzigartig ist
        public bool Check_Username_Unique(string UsernameToCheck)
        {
            bool usernameIsUnique = false;

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT COUNT(*) FROM dbbv_users WHERE username = '" +
                UsernameToCheck + "'";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();

            while (Reader.Read())
            {
                if (Convert.ToInt32(Reader.GetValue(0).ToString()) == 0)
                {
                    usernameIsUnique = true;
                }
                else
                {
                    usernameIsUnique = false;
                }
            }
            connection.Close();

            return usernameIsUnique;
        }

        //Löscht einen angelegten Benutzer
        public void deleteUser(int userID)
        {
            MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
            string myDeleteQuery = "DELETE FROM dbbv_users WHERE ID = " +
                userID;
            MySqlCommand myCommand = new MySqlCommand(myDeleteQuery);
            myCommand.Connection = myConnection;
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myCommand.Connection.Close();

            //Bestätigungsmeldung über erfolgreichen Delete
            MessageBox.Show("Benutzer: " + tbox_UserEdit_username.Text + " wurde erfolgreich gelöscht!",
                "Bestätigung",
                MessageBoxButtons.OK,
                MessageBoxIcon.Information);

            //Auf Hauptpanel leiten
            pnl_Home.BringToFront();
        }


        //Methode, die überprüft, ob ein String leer ist ( string == "" )
        public bool stringisempty(string toteststring)
        { 
            bool returnME = true;
            if (toteststring == "")
            {
                returnME = true;
            }
            else
            {
                returnME = false;
            }

            return returnME;
        }

        //Methode, die überprüft, ob 2 Strings identisch sind
        public bool stringsarethesame(string string1, string string2)
        {
            bool returnMe = true;
            if (string1 == string2)
            {
                returnMe = true;
            }
            else
            {
                returnMe = false;
            }

            return returnMe;
        }

        //Methode, die je nach Rechten die Optionen der Benutzer öffnet
        public void startOptions()
        {
            bool isadmin = false;

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT admin FROM dbbv_users WHERE username = '" +
                tbox_username.Text + "'";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();

            while (Reader.Read())
            {
                if (Reader.GetValue(0).ToString().ToLower() == "false")
                {
                    isadmin = false;
                }
                else
                {
                    if (Reader.GetValue(0).ToString().ToLower() == "true")
                    {
                        isadmin = true;
                    }
                    else
                    {
                        MessageBox.Show("admin ist nicht true oder false","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
                    }
                }
            }
            connection.Close();

            if (isadmin)
            {
                btn_usersStart.Visible = true;
                btn_borrowerStart.Left = 68;
                btn_locationsStart.Left = 518;
                pnl_options.BringToFront();
            }
            else
            {
                btn_usersStart.Visible = false;
                btn_borrowerStart.Left = 182;
                btn_locationsStart.Left = 412;
                pnl_options.BringToFront();
            }
        }


        /*bearbeitung hier*/
        void fill_cbox_booktype_new()
        {
            MySqlConnection con = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = con.CreateCommand();
            command.CommandText = "SELECT Type FROM dbbv_booktype";
            MySqlDataReader Reader;
            con.Open();
            Reader = command.ExecuteReader();
            cbox_booktype_new.Items.Clear();

            while (Reader.Read())
            {
                cbox_booktype_new.Items.Add(Reader.GetValue(0).ToString());
            }
            con.Close();

        }

        void fill_cbox_book_location_new()
        {
            MySqlConnection con2 = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = con2.CreateCommand();
            command.CommandText = "SELECT Ort FROM dbbv_ort";
            MySqlDataReader Reader;
            con2.Open();
            Reader = command.ExecuteReader();
            cbox_book_location_new.Items.Clear();

            while (Reader.Read())
            {
                cbox_book_location_new.Items.Add(Reader.GetValue(0).ToString());
            }
            con2.Close();

        }

        string get_ID_Ort(string loc)
        {
            MySqlConnection con = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = con.CreateCommand();
            command.CommandText = "SELECT ID FROM dbbv_ort WHERE Ort = " + "'" + loc + "'";
            MySqlDataReader Reader;
            con.Open();
            Reader = command.ExecuteReader();
            Reader.Read();
            string locID = Reader.GetValue(0).ToString();
            con.Close();
            return locID;
        }

        string get_ID_Type(string type)
        {
            MySqlConnection con2 = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = con2.CreateCommand();
            command.CommandText = "SELECT ID FROM dbbv_booktype WHERE Type = " + "'" + type + "'";
            MySqlDataReader Reader;
            con2.Open();
            Reader = command.ExecuteReader();
            Reader.Read();
            string typeID = Reader.GetValue(0).ToString();
            con2.Close();
            return typeID;
        }

        string get_Ort_By_ID(string ID)
        {
            MySqlConnection con3 = new MySqlConnection(myConnectionStringGlobal);
            con3.Open();
            MySqlCommand command = con3.CreateCommand();
            command.CommandText = "SELECT Ort FROM dbbv_ort WHERE ID = " + ID;
            MySqlDataReader Reader;
            Reader = command.ExecuteReader();
            Reader.Read();
            Console.WriteLine(Reader.GetValue(0));
            string ortstring = Reader.GetValue(0).ToString();
            con3.Close();
            return ortstring;
        }

        string get_Type_By_ID(string ID)
        {
            MySqlConnection con4 = new MySqlConnection(myConnectionStringGlobal);
            con4.Open();
            MySqlCommand command = con4.CreateCommand();
            command.CommandText = "SELECT Type FROM dbbv_booktype WHERE ID = " + ID;
            MySqlDataReader Reader;

            Reader = command.ExecuteReader();
            Reader.Read();
            Console.WriteLine(Reader.GetValue(0));
            string typestring = Reader.GetValue(0).ToString();
            con4.Close();
            return typestring;
        }
        /*bearbeitung ende*/

        bool HasNewVersion()
        {
            string FILE_NAME = "//10.110.0.31/austausch/Ausbildung/FI/Buecherverleih/hier_ist_das_programm/Buecherverleih/update/version.txt";

            if (File.Exists(FILE_NAME))
            {
                bool HasNewVersion = true;
                using (StreamReader sr = File.OpenText(FILE_NAME))
                {
                    String line;
                    while ((line = sr.ReadLine()) != null)
                    {
                        if (line == version)
                        {
                            HasNewVersion = false;
                        }
                        else
                        {
                            HasNewVersion = true;
                        }
                        break;
                    }
                }
                return HasNewVersion;
            }
            else
            {
                MessageBox.Show("Konnte Datei nicht finden: " + FILE_NAME,
                    "Warnung!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Warning);
                return false;
            }
        }

        void CheckUnallowedChar_TextBox(object sender)
        {
            if (((TextBox)sender).Text.Contains("'"))
            {
                ((TextBox)sender).Text = ((TextBox)sender).Text.Replace("'", "");

                MessageBox.Show("Keine Anführungszeichen erlaubt!",
                    "Ungültiges Zeichen",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);
            }

            if (((TextBox)sender).Text.Contains("\""))
            {
                ((TextBox)sender).Text = ((TextBox)sender).Text.Replace("\"", "");

                MessageBox.Show("Keine Anführungszeichen erlaubt!",
                    "Ungültiges Zeichen",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);
            }
        }

        void CheckUnallowedChar_RichTextBox(object sender)
        {
            if (((RichTextBox)sender).Text.Contains("'"))
            {
                ((RichTextBox)sender).Text = ((RichTextBox)sender).Text.Replace("'", "");

                MessageBox.Show("Keine Anführungszeichen erlaubt!",
                    "Ungültiges Zeichen",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);
            }

            if (((RichTextBox)sender).Text.Contains("\""))
            {
                ((RichTextBox)sender).Text = ((RichTextBox)sender).Text.Replace("\"", "");

                MessageBox.Show("Keine Anführungszeichen erlaubt!",
                    "Ungültiges Zeichen",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);
            }
        }



        void showbookdetail(string bookID)
        {
            string ausgeliehen = "";
            string borrowerID = "";
            string borrowedOn = "";

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_books WHERE ID = " + bookID;
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();

            while (Reader.Read())
            {
                tbox_book_labelID.Text = Reader.GetValue(1).ToString();
                tbox_book_name.Text = Reader.GetValue(2).ToString();
                rtBox_book_content.Text = Reader.GetValue(3).ToString();
                tbox_book_type.Text = get_Type_By_ID(Reader.GetValue(4).ToString());
                tbox_book_publisher.Text = Reader.GetValue(5).ToString();
                tbox_book_theme.Text = Reader.GetValue(6).ToString();
                tbox_book_Location.Text = get_Ort_By_ID(Reader.GetValue(7).ToString());
                ausgeliehen = Reader.GetValue(8).ToString();
                borrowerID = Reader.GetValue(9).ToString();
                try
                {
                    borrowedOn = Reader.GetValue(10).ToString();
                    borrowedOn = borrowedOn.Replace("00:00:00", "");
                }
                catch
                {
                    borrowedOn = "00.00.0000";
                }
            }
            connection.Close();

            //Unterscheidung, ob Buch ausgeliehen oder nicht
            if (ausgeliehen == "0")
            {
                //unsichtbar machen der Elemente
                label44.Visible = false;
                label45.Visible = false;
                tbox_book_borrower.Visible = false;
                tbox_book_borrowedOn.Visible = false;

                //Button soll sichtbar sein, wenn das Buch nicht ausgeliehen ist
                btn_eraseBook.Visible = true;
            }
            else
            {
                //sichtbar machen der Elemente
                label44.Visible = true;
                label45.Visible = true;
                tbox_book_borrower.Visible = true;
                tbox_book_borrowedOn.Visible = true;

                //Button soll unsichtbar sein, wenn das Buch ausgeliehen ist
                btn_eraseBook.Visible = false;

                //Zuweisen der Texte an Elemente
                tbox_book_borrowedOn.Text = borrowedOn;

                MySqlConnection connection2 = new MySqlConnection(myConnectionStringGlobal);
                MySqlCommand command2 = connection2.CreateCommand();
                command2.CommandText = "SELECT * FROM dbbv_borrowers WHERE ID = " + borrowerID;
                MySqlDataReader Reader2;
                connection2.Open();
                Reader2 = command2.ExecuteReader();

                while (Reader2.Read())
                {
                    tbox_book_borrower.Text = Reader2.GetValue(1).ToString() + ", " +
                        Reader2.GetValue(2).ToString();
                }
                connection2.Close();
            }


            pnl_bookDetail.BringToFront();
        }

    }
}
